package com.salary.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.salary.utils.MyLevenshtein;

/**
 * @author great
 *
 */

public class FileRead {
	// file1 输入的excel文件路径，file2 业务规则脚本txt文件路径
	public Map<String, Integer> filter(String[] filepathes, String file2, String outpath) throws IOException {
		Map<String, Integer> newexcelpath = new HashMap<String, Integer>();
		Integer countmap = new Integer(0);
		List<ArrayList<String>> records;
		ArrayList<String> record;
		// filter类解读 业务规则.txt 可以增加根据公司名或所属部门筛选
		Filter filter = new Filter(file2);
		String[] colarray = filter.getPropertyArray();
		String[] rowarray = filter.getCompanyArray();

		int partpos = 1;
		int condtion = 1;
		for(int i=0;i<colarray.length;i++){
			if(colarray[i].equals("影院简称")){
				partpos=i;
				condtion=i;
			}
		}
		ExcelRead excelread = new ExcelRead();
		for (int i = 0; i < filepathes.length; i++) {
			File file = new File(filepathes[i]);
			excelread.readExcel(file, colarray, i);
		}
		List<ArrayList<String>> elist = new ArrayList<ArrayList<String>>();
		elist = excelread.elist;

		
		// excel表格 中部门在第9列 姓名在第10列,namepos标记后面是数字列
		int namepos = 2;
		// 模糊算法 中文匹配
		for (int x = 0; x < rowarray.length; x++) {
			float similarity = 0;
			String similar = rowarray[x];
			for (int y = 0; y < elist.size(); y++) {
				ArrayList<String> el = elist.get(y);
				String cellv = el.get(condtion);
				float i = MyLevenshtein.levenshtein(rowarray[x], cellv);
				if (i > similarity) {
					similarity = i;
					similar = cellv;
				}
			}
			rowarray[x] = similar;
		}
		// 给选出的每家公司每个部门建一个excel表
		for (int x = 0; x < rowarray.length; x++) {
			records = new ArrayList<ArrayList<String>>();
			// 行规则筛选
			for (int y = 0; y < elist.size(); y++) {
				ArrayList<String> el = elist.get(y);
				String cellv = el.get(condtion);
				record = new ArrayList<String>();
				if (compareChinese(cellv, rowarray[x].trim()) || compareChinese(cellv, "总计")) {
					for (int z = 0; z < el.size(); z++) {
						record.add(z, el.get(z));
						// 每个部门加一条合计记录
					}
					if (record.size() > 0) {
						records.add(record);
					}
				} else if (y == 0) {
					for (int z = 0; z < el.size(); z++) {
						record.add(z, el.get(z));
					}
					records.add(0, record);
				} else if (y >= elist.size() - 1) {
					for (int z = 0; z < el.size(); z++) {
						if (z == namepos - 1) {
							record.add(z, rowarray[x] + " 汇总");
						} else if (z > namepos) {
							record.add("SUM");
						} else {
							record.add("");
						}
						// 每个部门加一条合计记录
					}
					records.add(record);
				}
			}
			//按发薪公司进行排序，并添加汇总行
			List<ArrayList<String>> records2= new ArrayList<ArrayList<String>>();
			ArrayList<String> record2 = new ArrayList<String>();;
			Map<String, String> map = new LinkedHashMap<>();	
			for(int i=1;i<records.size()-1;i++){
				map.put(records.get(i).get(0), "");
			}
			Set<String> set=map.keySet();
			ArrayList<String> columnList = new ArrayList<String>();
	        for(String str:set){
	        	columnList.add(str);
	        }
	        
	        records2.add(records.get(0));
	        for (int k = 0; k < filepathes.length; k++) {
	        	for(int i=0;i<columnList.size();i++){
	        		record2 = new ArrayList<String>();
	        		for(int j=1;j<records.size();j++){
	        			if(j==records.size()-1){
							for (int z = 0; z < colarray.length+1; z++) {
								if(z == 1){
									record2.add(records.get(j-1).get(1));
								}else if (z == namepos) {
									record2.add(z," 汇总");
								} else if (z == colarray.length) {
									record2.add(String.valueOf(k));
								} else if (z > namepos) {
									record2.add("SUM");
								} else {
									record2.add("");
								}
								// 每个部门加一条合计记录
							}
							records2.add(record2);
						
		        		}else if(records.get(j).get(0).equals(columnList.get(i))&&Integer.parseInt(records.get(j).get(colarray.length))==k){
	        			records2.add(records.get(j));
	        		} 
	        	}
	        }
	        }
	        	

			// 列规则筛选
			if (records2.size() > 2) {
				int filenum = filepathes.length;
				int lengthcol = records2.get(0).size();
				HSSFWorkbook wb = new HSSFWorkbook();
				for (int k = 0; k < filenum; k++) {
					HSSFSheet newsheet = wb.createSheet("company" + (k+1));
					HSSFRow row = null;
					HSSFCell cell = null;
					int count = 0;
					int count2 = 2;
					int count3 = 2;
					for (int i = 0; i < records2.size(); i++) {
						if (records2.get(i).get(lengthcol - 1).equals(k + "") || i == 0) {
							row = newsheet.createRow(count);
							count += 1;
							count3=count2;
							for (int j = 0; j < lengthcol-1 && j < 255; j++) {
								cell = row.createCell((short) j);
								String value = records2.get(i).get(j);
								
								if (value.equals("SUM")) {				
									// 汇总行 名字一栏为数字
									int total = records2.size() - 1;
									String col = to26Jinzhi(j);
									String formula = "SUM(" + col + (count3 + "") + ":" + col + (count-1 + "")
											+ ")";
									cell.setCellFormula(formula);
									count2=count+1;
								}
								else if (isNumeric(value)) {
									double numericV = Double.parseDouble(value);
									cell.setCellValue(numericV);
								} else {
									cell.setCellValue(value);
								}			
							}
						}
						if (i >= records2.size() - 1) {
							row = newsheet.createRow(count);
							cell = row.createCell(0);
							int ii = filepathes[k].lastIndexOf("/");
							String str = filepathes[k].substring(ii+1, filepathes[k].length());
							cell.setCellValue(str);
							cell = row.createCell(1);
							cell.setCellValue("汇总");
							for (int j = 2; j < lengthcol-1 && j < 255; j++) {
								cell = row.createCell((short) j);
								String col = to26Jinzhi(j);
								if(count < 1){
									count = 1;
								}
								String formula = "SUM(" + col + (2 + "") + ":" + col + (count + "")
										+ ")";
								cell.setCellFormula(formula);
								
							}
						}
					}
					if(count < 3){
						wb.removeSheetAt(wb.getNumberOfSheets()-1);
					}
				}
				String newexcelp = outpath + "输出文件_" + rowarray[x] + ".xls";
				newexcelpath.put(newexcelp, countmap);
				File file = new File(newexcelp);
				file.createNewFile();
				OutputStream outfile = new FileOutputStream(file);
				wb.write(outfile);
				outfile.close();
			}
		}
		return newexcelpath;
	}

	// 根据Unicode编码完美的判断中文汉字和符号
	private boolean isChinese(char c) {
		Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
		if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
				|| ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
				|| ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
				|| ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B
				|| ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
				|| ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS
				|| ub == Character.UnicodeBlock.GENERAL_PUNCTUATION) {
			return true;
		}
		return false;
	}

	// 中文比较，模糊查询
	private boolean compareChinese(String s1, String s2) throws UnsupportedEncodingException {
		Pattern p = Pattern.compile("\t|\r|\n");
		Matcher m1 = p.matcher(s1);
		Matcher m2 = p.matcher(s2);
		String st1 = m1.replaceAll("");
		String st2 = m2.replaceAll("");
		byte[] bs1 = st1.getBytes();
		byte[] bs2 = st2.getBytes();
		// 用新的字符编码生成字符串
		String ss1 = new String(bs1, "UTF-8");
		String ss2 = new String(bs2, "UTF-8");
		List<Character> list1 = new ArrayList<Character>();
		List<Character> list2 = new ArrayList<Character>();
		char[] ch = ss1.toCharArray();
		for (int j = 0; j < ch.length; j++) {
			char c = ch[j];
			list1.add(new Character(c));
		}
		char[] ch1 = ss2.toCharArray();
		for (int j = 0; j < ch1.length; j++) {
			char c = ch1[j];
			list2.add(new Character(c));
		}

		for (Iterator<Character> iter = list1.iterator(); iter.hasNext();) {
			Character ch2 = iter.next();
			if (!isChinese(ch2.charValue())) {
				iter.remove();
			}
		}
		for (Iterator<Character> iter = list2.iterator(); iter.hasNext();) {
			Character ch2 = iter.next();
			if (!isChinese(ch2.charValue())) {
				iter.remove();
			}
		}

		Character[] ch11 = list1.toArray(new Character[list1.size()]);
		Character[] ch12 = list2.toArray(new Character[list2.size()]);
		char[] ch13 = new char[ch11.length];
		char[] ch14 = new char[ch12.length];
		for (int k = 0; k < ch11.length; k++) {
			ch13[k] = ch11[k].charValue();
		}
		for (int k = 0; k < ch12.length; k++) {
			ch14[k] = ch12[k].charValue();
		}
		String sss1 = new String(ch13);
		String sss2 = new String(ch14);
		if (sss1.equals(sss2)) {
			return true;
		} else {
			return false;
		}

	}
	// 10进制转换为英文字母表示的26进制
			private static String to26Jinzhi(int data) {
				String str = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
				int scale = str.length(); // 转化目标进制
				String s = "";
				if (data == 0) {
					return str.charAt(0) + "";
				}
				while (data > 0) {
					if (data < scale) {
						s = str.charAt(data) + s;
						data = 0;
					} else if (scale == data) {
						int r = data % scale;
						s = "AA";
						data = (data - r - 26) / scale;
					} else if (scale < data && data < 2 * scale) {
						int r = data % scale;
						s = "A" + str.charAt(r);
						data = (data - r - 26) / scale;
					} else {
						int r = data % scale;
						s = str.charAt(r) + s;
						data = (data - r - 26) / scale;
					}
				}
				return s;
			}

	// 判断为数字
	public boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("(\\-|\\+)?\\d+(\\.\\d+)?");
		Matcher isNum = pattern.matcher(str);
		if (str.equals("")) {
			return false;
		} else if (isNum.matches()) {
			return true;
		} else {
			return false;
		}
	}
}